import os
if os.getenv('AG_ENV_PYTHON_PATH') is None:
    ROOT_DIR = os.path.dirname(os.path.abspath(__file__)) + os.sep
else:
    ROOT_DIR = os.getcwd() + os.sep
    # ROOT_DIR = WORK_DIR + "ag_art" + os.sep
parent_dir = os.path.dirname(ROOT_DIR)
import importlib.util
abstractjob_path = parent_dir+'/SKO/AbstractDPJob.py'
spec = importlib.util.spec_from_file_location('AbstractDPJob', abstractjob_path)
abstractjob_module = importlib.util.module_from_spec(spec)
spec.loader.exec_module(abstractjob_module)
AbstractDPJob = abstractjob_module.AbstractDPJob

# from SKO.AbstractDPJob import AbstractDPJob
import sys, datetime, json
import pandas as pd
import numpy as np
from numpy import array
import cvxpy as cp
from sqlalchemy import create_engine
from sqlalchemy.pool import NullPool
import os

class CesuanJob(AbstractDPJob):


    def __init__(self,
                 p_tmpl_no=None, p_cog_dest=None):

        super(CesuanJob, self).__init__()
        self.tmpl_no = p_tmpl_no
        self.cog_dest = p_cog_dest
        pass


    def execute(self):
        return self.do_execute()


    def do_execute(self):

        super(CesuanJob, self).do_execute()

        tmpl_no = self.tmpl_no
        cog_dest = self.cog_dest
        # 数据库配置写死
        DB_HOST_MPP_DB2 = '10.70.48.41'
        DB_PORT_MPP_DB2 = 50021
        DB_DBNAME_MPP_DB2 = 'BGBDPROD'
        DB_USER_MPP_DB2 = 'g0mazzai'
        DB_PASSWORD_MPP_DB2 = 'g0mazzaibg00'

        # 数据库连接函数写死
        def getConnectionDb2(host, port, dbname, user, password):
            # conn = pg.connect(host=host, port=port, dbname=dbname, user=user, password=password)
            engine = create_engine('ibm_db_sa://' + user + ':' + password + '@' + host + ':' + str(port) + '/' + dbname,
                                   encoding="utf-8", poolclass=NullPool)
            return engine.connect()

        db_conn_mpp = getConnectionDb2(DB_HOST_MPP_DB2,
                                       DB_PORT_MPP_DB2,
                                       DB_DBNAME_MPP_DB2,
                                       DB_USER_MPP_DB2,
                                       DB_PASSWORD_MPP_DB2)
        #读数据
        sql = " select VAR,CLASS,SOURCE,PROD_DSCR,PROD_CODE " \
              " from BG00MAZZAI.T_ADS_WH_YLMX_COKE_KIND_INFO " \
              " where TMPL_NO ='%s' and DATA_TYPE='CS' " % (tmpl_no)
        data_meizhong = pd.read_sql_query(sql, con=db_conn_mpp)
        data_meizhong.columns = data_meizhong.columns.str.upper()
        sql = " select SCHEME_NAME,COKING_PCC_RATIO,COKING_FATCOAL_RATIO,COKING_GFCOAL_RATIO, " \
              " COKING_GASCOAL_RATIO,COKING_LEANCOAL_RATIO,UNIT_PRICE,ASH,COKE_VM,S, "\
              " COKING_COKEYR,COKE_OUTPUT,COG_GEN " \
              " from BG00MAZZAI.T_ADS_FACT_YLMX_COKE_MODEL_S " \
              " where TMPL_NO ='%s' and DATA_TYPE='CS' " % (tmpl_no)
        data_jieguotongji = pd.read_sql_query(sql, con=db_conn_mpp)
        data_jieguotongji.columns = data_jieguotongji.columns.str.upper()
        sql = " select VAR,CLASS,SOURCE,PROD_DSCR,PROD_CODE,ASH,COKE_VM,S " \
              " from BG00MAZZAI.T_ADS_WH_YLMX_COKE_IND_INFO " \
              " where TMPL_NO ='%s' and DATA_TYPE='CS' " % (tmpl_no)
        data_meizhi = pd.read_sql_query(sql, con=db_conn_mpp)
        data_meizhi.columns = data_meizhi.columns.str.upper()
        sql = " select VAR,CLASS,SOURCE,PROD_DSCR,PROD_CODE,ACT_CONSUME,UNIT_PRICE " \
              " from BG00MAZZAI.T_ADS_WH_YLMX_COKE_PRICE " \
              " where TMPL_NO ='%s' and DATA_TYPE='CS' " % (tmpl_no)
        data_sourceprice = pd.read_sql_query(sql, con=db_conn_mpp)
        data_sourceprice.columns = data_sourceprice.columns.str.upper()
        sql = " select COKEYR_CONST,COG_GEN_CONST,SINTER_CRSCOKE_RATIO, " \
              " COKING_METCOKRAT,PCOKE_COKEPOWD_RATE,ELECOUT,COAL_USE, " \
              " COKE_OUTPUT,POWOUTSRC_PRICE,NG_PRICE,THMCOAL_PRICE " \
              " from BG00MAZZAI.T_ADS_WH_YLMX_COKE_COEF " \
              " where TMPL_NO ='%s' and DATA_TYPE='CS' " % (tmpl_no)
        data_canshu = pd.read_sql_query(sql, con=db_conn_mpp)
        data_canshu.columns = data_canshu.columns.str.upper()

        chengjiaolv_constant = data_canshu.loc[0]['COKEYR_CONST']
        cogfasheng_constant = data_canshu.loc[0]['COG_GEN_CONST']
        cujiaolv = data_canshu.loc[0]['SINTER_CRSCOKE_RATIO']
        yejinjiaolv = data_canshu.loc[0]['COKING_METCOKRAT']
        waigoujiaofenlv = data_canshu.loc[0]['PCOKE_COKEPOWD_RATE']
        cogfadian = data_canshu.loc[0]['ELECOUT']
        fadianmeihao = data_canshu.loc[0]['COAL_USE']
        jiaotanchanliang = data_canshu.loc[0]['COKE_OUTPUT']
        price_waigoudian = data_canshu.loc[0]['POWOUTSRC_PRICE']
        price_tianranqi = data_canshu.loc[0]['NG_PRICE']
        price_fadianmei = data_canshu.loc[0]['THMCOAL_PRICE']
        data_other1 = data_sourceprice[data_sourceprice['PROD_DSCR'] == '外购焦炭']
        data_other1 = data_other1.reset_index(drop=True)
        price_waigoujiaotan = data_other1.loc[0]['UNIT_PRICE']
        data_other2 = data_sourceprice[data_sourceprice['PROD_DSCR'] == '烧结燃料']
        data_other2 = data_other2.reset_index(drop=True)
        price_shaojieranliao = data_other2.loc[0]['UNIT_PRICE']
        data_other3 = data_sourceprice[data_sourceprice['PROD_DSCR'] == 'COG']
        data_other3 = data_other3.reset_index(drop=True)
        price_cog = data_other3.loc[0]['UNIT_PRICE']
        data_sourceprice = data_sourceprice[data_sourceprice['VAR'] != '其他参数']
        data_sourceprice = data_sourceprice.reset_index(drop=True)
        data_year = data_jieguotongji[data_jieguotongji['SCHEME_NAME'] == '年度预算']
        data_year = data_year.reset_index(drop=True)
        year_var1 = data_year.loc[0]['COKING_PCC_RATIO']
        year_var2 = data_year.loc[0]['COKING_FATCOAL_RATIO']
        year_var3 = data_year.loc[0]['COKING_GFCOAL_RATIO']
        year_var4 = data_year.loc[0]['COKING_GASCOAL_RATIO']
        year_var5 = data_year.loc[0]['COKING_LEANCOAL_RATIO']
        data_month = data_jieguotongji[data_jieguotongji['SCHEME_NAME'] == '月预算']
        data_month = data_month.reset_index(drop=True)
        month_var1 = data_month.loc[0]['COKING_PCC_RATIO']
        month_var2 = data_month.loc[0]['COKING_FATCOAL_RATIO']
        month_var3 = data_month.loc[0]['COKING_GFCOAL_RATIO']
        month_var4 = data_month.loc[0]['COKING_GASCOAL_RATIO']
        month_var5 = data_month.loc[0]['COKING_LEANCOAL_RATIO']
        data_adjust = data_jieguotongji[data_jieguotongji['SCHEME_NAME'] == '调整方案']
        data_adjust = data_adjust.reset_index(drop=True)
        adjust_var1 = data_adjust.loc[0]['COKING_PCC_RATIO']
        adjust_var2 = data_adjust.loc[0]['COKING_FATCOAL_RATIO']
        adjust_var3 = data_adjust.loc[0]['COKING_GFCOAL_RATIO']
        adjust_var4 = data_adjust.loc[0]['COKING_GASCOAL_RATIO']
        adjust_var5 = data_adjust.loc[0]['COKING_LEANCOAL_RATIO']

        data_meizhi = data_meizhi[['PROD_CODE', 'ASH', 'COKE_VM', 'S']]
        data_sourceprice = data_sourceprice[['PROD_CODE', 'ACT_CONSUME', 'UNIT_PRICE']]
        v = ['PROD_CODE']
        df3 = pd.merge(data_meizhong, data_sourceprice, on=v, how='left')
        v = ['PROD_CODE']
        df4 = pd.merge(df3, data_meizhi, on=v, how='left')
        df4['TOTAL_PRICE'] = df4['ACT_CONSUME'] * df4['UNIT_PRICE']
        df4['TOTAL_ASH'] = df4['ACT_CONSUME'] * df4['ASH']
        df4['TOTAL_COKE_VM'] = df4['ACT_CONSUME'] * df4['COKE_VM']
        df4['TOTAL_S'] = df4['ACT_CONSUME'] * df4['S']

        data_var1 = df4[df4['VAR'] == '主焦']
        data_var1 = data_var1.reset_index(drop=True)
        var1_sum_wt = data_var1['ACT_CONSUME'].sum()
        var1_sum_price = data_var1['TOTAL_PRICE'].sum()
        var1_sum_ash = data_var1['TOTAL_ASH'].sum()
        var1_sum_coke_vm = data_var1['TOTAL_COKE_VM'].sum()
        var1_sum_s = data_var1['TOTAL_S'].sum()
        var1_unit_price = var1_sum_price / var1_sum_wt
        var1_ash = var1_sum_ash / var1_sum_wt
        var1_coke_vm = var1_sum_coke_vm / var1_sum_wt
        var1_s = var1_sum_s / var1_sum_wt
        data_var2 = df4[df4['VAR'] == '肥煤']
        data_var2 = data_var2.reset_index(drop=True)
        var2_sum_wt = data_var2['ACT_CONSUME'].sum()
        var2_sum_price = data_var2['TOTAL_PRICE'].sum()
        var2_sum_ash = data_var2['TOTAL_ASH'].sum()
        var2_sum_coke_vm = data_var2['TOTAL_COKE_VM'].sum()
        var2_sum_s = data_var2['TOTAL_S'].sum()
        var2_unit_price = var2_sum_price / var2_sum_wt
        var2_ash = var2_sum_ash / var2_sum_wt
        var2_coke_vm = var2_sum_coke_vm / var2_sum_wt
        var2_s = var2_sum_s / var2_sum_wt
        data_var3 = df4[df4['VAR'] == '1/3焦']
        data_var3 = data_var3.reset_index(drop=True)
        var3_sum_wt = data_var3['ACT_CONSUME'].sum()
        var3_sum_price = data_var3['TOTAL_PRICE'].sum()
        var3_sum_ash = data_var3['TOTAL_ASH'].sum()
        var3_sum_coke_vm = data_var3['TOTAL_COKE_VM'].sum()
        var3_sum_s = data_var3['TOTAL_S'].sum()
        var3_unit_price = var3_sum_price / var3_sum_wt
        var3_ash = var3_sum_ash / var3_sum_wt
        var3_coke_vm = var3_sum_coke_vm / var3_sum_wt
        var3_s = var3_sum_s / var3_sum_wt
        data_var4 = df4[df4['VAR'] == '气煤']
        data_var4 = data_var4.reset_index(drop=True)
        var4_sum_wt = data_var4['ACT_CONSUME'].sum()
        var4_sum_price = data_var4['TOTAL_PRICE'].sum()
        var4_sum_ash = data_var4['TOTAL_ASH'].sum()
        var4_sum_coke_vm = data_var4['TOTAL_COKE_VM'].sum()
        var4_sum_s = data_var4['TOTAL_S'].sum()
        var4_unit_price = var4_sum_price / var4_sum_wt
        var4_ash = var4_sum_ash / var4_sum_wt
        var4_coke_vm = var4_sum_coke_vm / var4_sum_wt
        var4_s = var4_sum_s / var4_sum_wt
        data_var5 = df4[df4['VAR'] == '瘦煤']
        data_var5 = data_var5.reset_index(drop=True)
        var5_sum_wt = data_var5['ACT_CONSUME'].sum()
        var5_sum_price = data_var5['TOTAL_PRICE'].sum()
        var5_sum_ash = data_var5['TOTAL_ASH'].sum()
        var5_sum_coke_vm = data_var5['TOTAL_COKE_VM'].sum()
        var5_sum_s = data_var5['TOTAL_S'].sum()
        var5_unit_price = var5_sum_price / var5_sum_wt
        var5_ash = var5_sum_ash / var5_sum_wt
        var5_coke_vm = var5_sum_coke_vm / var5_sum_wt
        var5_s = var5_sum_s / var5_sum_wt

        year_unit_price = year_var1 / 100 * var1_unit_price + year_var2 / 100 * var2_unit_price + year_var3 / 100 * var3_unit_price + year_var4 / 100 * var4_unit_price + year_var5 / 100 * var5_unit_price
        year_ash = year_var1 / 100 * var1_ash + year_var2 / 100 * var2_ash + year_var3 / 100 * var3_ash + year_var4 / 100 * var4_ash + year_var5 / 100 * var5_ash
        year_coke_vm = year_var1 / 100 * var1_coke_vm + year_var2 / 100 * var2_coke_vm + year_var3 / 100 * var3_coke_vm + year_var4 / 100 * var4_coke_vm + year_var5 / 100 * var5_coke_vm
        year_s = year_var1 / 100 * var1_s + year_var2 / 100 * var2_s + year_var3 / 100 * var3_s + year_var4 / 100 * var4_s + year_var5 / 100 * var5_s

        month_unit_price = month_var1 / 100 * var1_unit_price + month_var2 / 100 * var2_unit_price + month_var3 / 100 * var3_unit_price + month_var4 / 100 * var4_unit_price + month_var5 / 100 * var5_unit_price
        month_ash = month_var1 / 100 * var1_ash + month_var2 / 100 * var2_ash + month_var3 / 100 * var3_ash + month_var4 / 100 * var4_ash + month_var5 / 100 * var5_ash
        month_coke_vm = month_var1 / 100 * var1_coke_vm + month_var2 / 100 * var2_coke_vm + month_var3 / 100 * var3_coke_vm + month_var4 / 100 * var4_coke_vm + month_var5 / 100 * var5_coke_vm
        month_s = month_var1 / 100 * var1_s + month_var2 / 100 * var2_s + month_var3 / 100 * var3_s + month_var4 / 100 * var4_s + month_var5 / 100 * var5_s

        adjust_unit_price = adjust_var1 / 100 * var1_unit_price + adjust_var2 / 100 * var2_unit_price + adjust_var3 / 100 * var3_unit_price + adjust_var4 / 100 * var4_unit_price + adjust_var5 / 100 * var5_unit_price
        adjust_ash = adjust_var1 / 100 * var1_ash + adjust_var2 / 100 * var2_ash + adjust_var3 / 100 * var3_ash + adjust_var4 / 100 * var4_ash + adjust_var5 / 100 * var5_ash
        adjust_coke_vm = adjust_var1 / 100 * var1_coke_vm + adjust_var2 / 100 * var2_coke_vm + adjust_var3 / 100 * var3_coke_vm + adjust_var4 / 100 * var4_coke_vm + adjust_var5 / 100 * var5_coke_vm
        adjust_s = adjust_var1 / 100 * var1_s + adjust_var2 / 100 * var2_s + adjust_var3 / 100 * var3_s + adjust_var4 / 100 * var4_s + adjust_var5 / 100 * var5_s

        year_chengjiaolv = 97 - year_coke_vm * 5 / 6 + chengjiaolv_constant
        month_chengjiaolv = 97 - month_coke_vm * 5 / 6 + chengjiaolv_constant
        adjust_chengjiaolv = 97 - adjust_coke_vm * 5 / 6 + chengjiaolv_constant

        ganmeiliang = jiaotanchanliang / month_chengjiaolv * 100
        year_jiaotanchanliang = ganmeiliang * year_chengjiaolv / 100
        month_jiaotanchanliang = jiaotanchanliang
        adjust_jiaotanchanliang = ganmeiliang * adjust_chengjiaolv / 100
        year_cogfasheng = (9.37 * year_coke_vm + 66.7) * ganmeiliang / 30 / 24 + cogfasheng_constant
        month_cogfasheng = (9.37 * month_coke_vm + 66.7) * ganmeiliang / 30 / 24 + cogfasheng_constant
        adjust_cogfasheng = (9.37 * adjust_coke_vm + 66.7) * ganmeiliang / 30 / 24 + cogfasheng_constant

        # data_jieguotongji_out = data_jieguotongji[['SCHEME_NAME', 'COKING_PCC_RATIO', 'COKING_FATCOAL_RATIO', 'COKING_GFCOAL_RATIO', 'COKING_GASCOAL_RATIO', 'COKING_LEANCOAL_RATIO']]
        data_jieguotongji_out = pd.DataFrame(
            columns=['SCHEME_NAME', 'COKING_PCC_RATIO', 'COKING_FATCOAL_RATIO', 'COKING_GFCOAL_RATIO',
                     'COKING_GASCOAL_RATIO', 'COKING_LEANCOAL_RATIO',
                     'UNIT_PRICE', 'ASH', 'COKE_VM', 'S',
                     'COKING_COKEYR', 'COKE_OUTPUT', 'COG_GEN'])
        dict = {}
        dict['SCHEME_NAME'] = '年度预算'
        dict['COKING_PCC_RATIO'] = year_var1
        dict['COKING_FATCOAL_RATIO'] = year_var2
        dict['COKING_GFCOAL_RATIO'] = year_var3
        dict['COKING_GASCOAL_RATIO'] = year_var4
        dict['COKING_LEANCOAL_RATIO'] = year_var5
        dict['UNIT_PRICE'] = year_unit_price
        dict['ASH'] = year_ash
        dict['COKE_VM'] = year_coke_vm
        dict['S'] = year_s
        dict['COKING_COKEYR'] = year_chengjiaolv
        dict['COKE_OUTPUT'] = year_jiaotanchanliang
        dict['COG_GEN'] = year_cogfasheng
        new_row = pd.Series(dict)
        data_jieguotongji_out = data_jieguotongji_out.append(new_row, ignore_index=True)

        dict = {}
        dict['SCHEME_NAME'] = '月预算'
        dict['COKING_PCC_RATIO'] = month_var1
        dict['COKING_FATCOAL_RATIO'] = month_var2
        dict['COKING_GFCOAL_RATIO'] = month_var3
        dict['COKING_GASCOAL_RATIO'] = month_var4
        dict['COKING_LEANCOAL_RATIO'] = month_var5
        dict['UNIT_PRICE'] = month_unit_price
        dict['ASH'] = month_ash
        dict['COKE_VM'] = month_coke_vm
        dict['S'] = month_s
        dict['COKING_COKEYR'] = month_chengjiaolv
        dict['COKE_OUTPUT'] = month_jiaotanchanliang
        dict['COG_GEN'] = month_cogfasheng
        new_row = pd.Series(dict)
        data_jieguotongji_out = data_jieguotongji_out.append(new_row, ignore_index=True)

        dict = {}
        dict['SCHEME_NAME'] = '调整方案'
        dict['COKING_PCC_RATIO'] = adjust_var1
        dict['COKING_FATCOAL_RATIO'] = adjust_var2
        dict['COKING_GFCOAL_RATIO'] = adjust_var3
        dict['COKING_GASCOAL_RATIO'] = adjust_var4
        dict['COKING_LEANCOAL_RATIO'] = adjust_var5
        dict['UNIT_PRICE'] = adjust_unit_price
        dict['ASH'] = adjust_ash
        dict['COKE_VM'] = adjust_coke_vm
        dict['S'] = adjust_s
        dict['COKING_COKEYR'] = adjust_chengjiaolv
        dict['COKE_OUTPUT'] = adjust_jiaotanchanliang
        dict['COG_GEN'] = adjust_cogfasheng
        new_row = pd.Series(dict)
        data_jieguotongji_out = data_jieguotongji_out.append(new_row, ignore_index=True)

        delta_jiaotanchanliang = adjust_jiaotanchanliang - month_jiaotanchanliang
        delta_culiaoliang = delta_jiaotanchanliang * cujiaolv
        delta_waigoujiao = delta_jiaotanchanliang * yejinjiaolv / (1 - waigoujiaofenlv)
        delta_cujiaocaigou = delta_waigoujiao * waigoujiaofenlv - delta_culiaoliang
        delta_waigoujiao_cost = -delta_waigoujiao * price_waigoujiaotan
        delta_cujiaocaigou_cost = delta_cujiaocaigou * price_shaojieranliao
        delta_peihemei_cost = (adjust_unit_price - month_unit_price) * ganmeiliang
        delta_COGfashengliang = (month_cogfasheng - adjust_cogfasheng) * 30 * 24
        # COG_DEST焦炉煤气去向
        # 发电；补充焦炉煤气

        if cog_dest != '发电':
            delta_COG_cost = delta_COGfashengliang * price_cog
        # elif cog_dest=='发电':
        else:
            delta_COG_fadian = delta_COGfashengliang * cogfadian
            tianranqifadian = delta_COGfashengliang / 2
            fadianmeifadian = delta_COG_fadian * fadianmeihao / 100
            waigoudian = delta_COG_fadian
            # 都是delta_COGfashengliang的倍数，比较倍数取最小的
            tianranqi_coef = 0.5 * price_tianranqi
            fadianmei_coef = cogfadian * fadianmeihao / 100 * price_tianranqi / 10000
            waigoudian_coef = cogfadian * price_waigoudian
            delta_COG_cost1 = tianranqifadian * price_tianranqi
            delta_COG_cost2 = fadianmeifadian * price_fadianmei / 10000
            delta_COG_cost3 = waigoudian * price_waigoudian
            if delta_COGfashengliang >= 0:
                if tianranqi_coef <= fadianmei_coef and tianranqi_coef <= waigoudian_coef:
                    delta_COG_cost = delta_COG_cost1
                elif fadianmei_coef <= tianranqi_coef and fadianmei_coef <= waigoudian_coef:
                    delta_COG_cost = delta_COG_cost2
                elif waigoudian_coef <= tianranqi_coef and waigoudian_coef <= fadianmei_coef:
                    delta_COG_cost = delta_COG_cost3
            else:
                if tianranqi_coef >= fadianmei_coef and tianranqi_coef >= waigoudian_coef:
                    delta_COG_cost = delta_COG_cost1
                elif fadianmei_coef >= tianranqi_coef and fadianmei_coef >= waigoudian_coef:
                    delta_COG_cost = delta_COG_cost2
                elif waigoudian_coef >= tianranqi_coef and waigoudian_coef >= fadianmei_coef:
                    delta_COG_cost = delta_COG_cost3
        total_cost = delta_peihemei_cost + delta_waigoujiao_cost + delta_cujiaocaigou_cost + delta_COG_cost

        df_out2 = pd.DataFrame(columns=['SCHEME_NAME',
                                        'COKE_OUTPUT', 'CRSCOKE_OUTPUT', 'COKING_DRY_COAL_AMT', 'COG_GEN',
                                        'OUTSCOKE_CONSUME', 'SINTER_FUEL_PUR_INCR', 'UNIT_PRICE', 'DEST',
                                        'OUTSCOKE_COST', 'SINTER_FUEL_PUR_COST', 'COALBLD_COST', 'COG_IMPC_COST',
                                        'IRONMAKE_COST_CHANGE', 'TOTAL_COST',
                                        'NG_CONSUME', 'THMCOAL_CONSUME', 'EQ'])

        if cog_dest != '发电':
            dict = {}
            dict['SCHEME_NAME'] = '调整方案对比月预算'
            dict['COKE_OUTPUT'] = delta_jiaotanchanliang
            dict['CRSCOKE_OUTPUT'] = delta_culiaoliang
            dict['COKING_DRY_COAL_AMT'] = ganmeiliang
            dict['COG_GEN'] = delta_COGfashengliang
            dict['OUTSCOKE_CONSUME'] = delta_waigoujiao
            dict['SINTER_FUEL_PUR_INCR'] = delta_cujiaocaigou
            dict['UNIT_PRICE'] = adjust_unit_price - month_unit_price
            dict['DSET'] = cog_dest
            dict['OUTSCOKE_COST'] = delta_waigoujiao_cost
            dict['SINTER_FUEL_PUR_COST'] = delta_cujiaocaigou_cost
            dict['COALBLD_COST'] = delta_peihemei_cost
            dict['COG_IMPC_COST'] = delta_COG_cost
            dict['IRONMAKE_COST_CHANGE'] = delta_peihemei_cost + delta_waigoujiao_cost + delta_cujiaocaigou_cost
            dict['TOTAL_COST'] = total_cost
            new_row = pd.Series(dict)
            df_out2 = df_out2.append(new_row, ignore_index=True)
        else:
            delta_COG_fadian = delta_COGfashengliang * cogfadian
            tianranqifadian = delta_COGfashengliang / 2
            fadianmeifadian = delta_COG_fadian * fadianmeihao / 100
            waigoudian = delta_COG_fadian
            dict = {}
            dict['SCHEME_NAME'] = '调整方案对比月预算'
            dict['COKE_OUTPUT'] = delta_jiaotanchanliang
            dict['CRSCOKE_OUTPUT'] = delta_culiaoliang
            dict['COKING_DRY_COAL_AMT'] = ganmeiliang
            dict['COG_GEN'] = delta_COGfashengliang
            dict['OUTSCOKE_CONSUME'] = delta_waigoujiao
            dict['SINTER_FUEL_PUR_INCR'] = delta_cujiaocaigou
            dict['UNIT_PRICE'] = adjust_unit_price - month_unit_price
            dict['DEST'] = '发电最优'
            dict['OUTSCOKE_COST'] = delta_waigoujiao_cost
            dict['SINTER_FUEL_PUR_COST'] = delta_cujiaocaigou_cost
            dict['COALBLD_COST'] = delta_peihemei_cost
            dict['COG_IMPC_COST'] = delta_COG_cost
            dict['IRONMAKE_COST_CHANGE'] = delta_peihemei_cost + delta_waigoujiao_cost + delta_cujiaocaigou_cost
            dict['TOTAL_COST'] = total_cost
            new_row = pd.Series(dict)
            df_out2 = df_out2.append(new_row, ignore_index=True)
            dict = {}
            dict['SCHEME_NAME'] = '调整方案对比月预算'
            dict['COKE_OUTPUT'] = delta_jiaotanchanliang
            dict['CRSCOKE_OUTPUT'] = delta_culiaoliang
            dict['COKING_DRY_COAL_AMT'] = ganmeiliang
            dict['COG_GEN'] = delta_COGfashengliang
            dict['OUTSCOKE_CONSUME'] = delta_waigoujiao
            dict['SINTER_FUEL_PUR_INCR'] = delta_cujiaocaigou
            dict['UNIT_PRICE'] = adjust_unit_price - month_unit_price
            dict['DEST'] = '发电天然气代替'
            dict['OUTSCOKE_COST'] = delta_waigoujiao_cost
            dict['SINTER_FUEL_PUR_COST'] = delta_cujiaocaigou_cost
            dict['COALBLD_COST'] = delta_peihemei_cost
            dict['COG_IMPC_COST'] = tianranqifadian * price_tianranqi
            dict['IRONMAKE_COST_CHANGE'] = delta_peihemei_cost + delta_waigoujiao_cost + delta_cujiaocaigou_cost
            dict['TOTAL_COST'] = delta_peihemei_cost + delta_waigoujiao_cost + delta_cujiaocaigou_cost + tianranqifadian * price_tianranqi
            dict['NG_CONSUME'] = tianranqifadian
            new_row = pd.Series(dict)
            df_out2 = df_out2.append(new_row, ignore_index=True)
            dict = {}
            dict['SCHEME_NAME'] = '调整方案对比月预算'
            dict['COKE_OUTPUT'] = delta_jiaotanchanliang
            dict['CRSCOKE_OUTPUT'] = delta_culiaoliang
            dict['COKING_DRY_COAL_AMT'] = ganmeiliang
            dict['COG_GEN'] = delta_COGfashengliang
            dict['OUTSCOKE_CONSUME'] = delta_waigoujiao
            dict['SINTER_FUEL_PUR_INCR'] = delta_cujiaocaigou
            dict['UNIT_PRICE'] = adjust_unit_price - month_unit_price
            dict['DEST'] = '发电发电煤代替'
            dict['OUTSCOKE_COST'] = delta_waigoujiao_cost
            dict['SINTER_FUEL_PUR_COST'] = delta_cujiaocaigou_cost
            dict['COALBLD_COST'] = delta_peihemei_cost
            dict['COG_IMPC_COST'] = fadianmeifadian * price_fadianmei / 10000
            dict['IRONMAKE_COST_CHANGE'] = delta_peihemei_cost + delta_waigoujiao_cost + delta_cujiaocaigou_cost
            dict['TOTAL_COST'] = delta_peihemei_cost + delta_waigoujiao_cost + delta_cujiaocaigou_cost + fadianmeifadian * price_fadianmei / 10000
            dict['THMCOAL_CONSUME'] = fadianmeifadian
            new_row = pd.Series(dict)
            df_out2 = df_out2.append(new_row, ignore_index=True)
            dict = {}
            dict['SCHEME_NAME'] = '调整方案对比月预算'
            dict['COKE_OUTPUT'] = delta_jiaotanchanliang
            dict['CRSCOKE_OUTPUT'] = delta_culiaoliang
            dict['COKING_DRY_COAL_AMT'] = ganmeiliang
            dict['COG_GEN'] = delta_COGfashengliang
            dict['OUTSCOKE_CONSUME'] = delta_waigoujiao
            dict['SINTER_FUEL_PUR_INCR'] = delta_cujiaocaigou
            dict['UNIT_PRICE'] = adjust_unit_price - month_unit_price
            dict['DEST'] = '发电外购电代替'
            dict['OUTSCOKE_COST'] = delta_waigoujiao_cost
            dict['SINTER_FUEL_PUR_COST'] = delta_cujiaocaigou_cost
            dict['COALBLD_COST'] = delta_peihemei_cost
            dict['COG_IMPC_COST'] = waigoudian * price_waigoudian
            dict['IRONMAKE_COST_CHANGE'] = delta_peihemei_cost + delta_waigoujiao_cost + delta_cujiaocaigou_cost
            dict['TOTAL_COST'] = delta_peihemei_cost + delta_waigoujiao_cost + delta_cujiaocaigou_cost + waigoudian * price_waigoudian
            dict['EQ'] = waigoudian
            new_row = pd.Series(dict)
            df_out2 = df_out2.append(new_row, ignore_index=True)

        data_jieguotongji_out['TMPL_NO'] = tmpl_no
        data_jieguotongji_out['DATA_TYPE'] = 'CS'


        now = datetime.datetime.now()
        now_1 = now.strftime('%Y%m%d%H%M%S')
        data_jieguotongji_out['REC_CREATE_TIME'] = now_1
        data_jieguotongji_out['REC_CREATOR'] = 'zzai'
        data_jieguotongji_out['REC_REVISE_TIME'] = now_1
        data_jieguotongji_out['REC_REVISOR'] = 'zzai'
        data_jieguotongji_out_rounded = data_jieguotongji_out.round(3)

        sql = " DELETE FROM " \
              " BG00MAZZAI.T_ADS_FACT_YLMX_COKE_MODEL_S" \
              " WHERE 1=1 AND TMPL_NO ='%s' and DATA_TYPE='CS' " % (tmpl_no)
        db_conn_mpp.execute(sql)

        #####存入到数据库
        data_jieguotongji_out_rounded.to_sql(name='T_ADS_FACT_YLMX_COKE_MODEL_S'.lower(),
                               con=db_conn_mpp,
                               schema='BG00MAZZAI'.lower(),
                               index=False,
                               if_exists='append',
                               chunksize=10000)
        # writer = pd.ExcelWriter('炼焦结果统计表测算.xlsx')
        # data_jieguotongji_out_rounded.to_excel(writer, sheet_name='Sheet1', index=False)
        # writer.save()
        df_out2['TMPL_NO'] = tmpl_no
        df_out2['DATA_TYPE'] = 'CS'
        df_out2['REC_CREATE_TIME'] = now_1
        df_out2['REC_CREATOR'] = 'zzai'
        df_out2['REC_REVISE_TIME'] = now_1
        df_out2['REC_REVISOR'] = 'zzai'
        df_out2_rounded = df_out2.round(3)
        sql = " DELETE FROM " \
              " BG00MAZZAI.T_ADS_FACT_YLMX_COKE_MODEL_C" \
              " WHERE 1=1 AND TMPL_NO ='%s' and DATA_TYPE='CS' " % (tmpl_no)
        db_conn_mpp.execute(sql)

        #####存入到数据库
        df_out2_rounded.to_sql(name='T_ADS_FACT_YLMX_COKE_MODEL_C'.lower(),
                                             con=db_conn_mpp,
                                             schema='BG00MAZZAI'.lower(),
                                             index=False,
                                             if_exists='append',
                                             chunksize=10000)
        # writer = pd.ExcelWriter('炼焦方案对比表测算.xlsx')
        # df_out2.to_excel(writer, sheet_name='Sheet1', index=False)
        # writer.save()
        message = '计算结果入库成功'
        # print('finish')
        return message























